//
//  DataBaseHandle.m
//  UI_18_SQLite
//
//  Created by 李劲成 on 16/4/1.
//  Copyright © 2016年 ljc. All rights reserved.
//

#import "DataBaseHandle.h"
#import "SandboxHandler.h"
#import <sqlite3.h>
#import "ModelOfStudent.h"
@implementation DataBaseHandle

// 创建一个 sqlite3对象.(结构体)
sqlite3 *db;

#pragma mark - 构造器.
+ (instancetype)sharaDateBaseHandle {
    
    // 静态区创建变量, 只初始化一次.
    static DataBaseHandle *dbHandler = nil;
    if (dbHandler == nil) {
        dbHandler = [[DataBaseHandle alloc]init];
        
    }
    return dbHandler;

}

#pragma mark - SQLite 操作.
/** 打开数据库. API:sqlite3_open */
- (void)openSQLName:(NSString *)name {
    
     // API: sqlite3_open
    
    //判断 db 指针是否为空
    if (db != nil) {
        NSLog(@"数据库已经开启");
        return;
    }
    
    // 创建数据库文件的路径.
    NSString *path = [SandboxHandler pathForDirectory:Documents WithFileName:name];
    
    // 注意参数1是C字符串, 参数2是 **, 所以传入的参数是地址.
    int result = sqlite3_open(path.UTF8String, &db);
    
    // 根据函数返回值, 判断执行是否正确.
    if (result == SQLITE_OK) {
        NSLog(@"数据库开启成功.");
    }
    else {
        
        NSLog(@"数据库打开失败, code = %d", result);
        
    }

}

#pragma mark - 创建表格.
/** 创建表格. API:sqlite3_exec */
- (void)createTabel {
    // 创建表格的 SQL语句.
    NSString *sql = @"CREATE  TABLE IF NOT EXISTS dls160101(ID_S INTEGER PRIMARY KEY AUTOINCREMENT, name text, gender text, age INTEGER)";
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);

    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
    }
    else {
        NSLog(@"创建表格失败, 错误码:%d", result);
    }



}

#pragma mark - 插入数据
/** 插入数据. */
- (void)insertModel:(ModelOfStudent *)stu {
    //插入数据的 SQL 语句
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO dls160101 (name, gender, age) VALUES ('%@', '%@', '%ld')", stu.name, stu.gender, stu.age];

    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
    else {
        NSLog(@"插入数据失败, 错误码:%d", result);
    }
    


}

#pragma mark - 更新数据.
/** 更新数据. API: sqlite3_exec */
-(void)updateModel:(ModelOfStudent *)stu id:(NSInteger)ID {
    // 更新数据 SQL 语句.
    NSString *sql = [NSString stringWithFormat:@"UPDATE dls160101 SET name = '%@',  gender = '%@', age = '%ld' WHERE ID_S = '%ld'", stu.name, stu.gender, stu.age, ID];
    
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"更新数据成功");
    }
    else {
        NSLog(@"更新数据失败, 错误码:%d", result);
    }



}
#pragma mark - 删除数据.
/** 删除数据. API: sqlite3_exec */
- (void)deleteModelWithID:(NSInteger)ID {
    
    // 删除语句.
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM dls160101 WHERE ID_S = '%ld'", ID];
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除数据成功");
    }
    else {
        NSLog(@"删除数据失败, 错误码:%d", result);
    }
    



}

/** 检索数据. API: sqlite3_stmt */
- (NSArray<ModelOfStudent *> *)selectWithGender:(NSString *)gender {
    
    NSMutableArray *array = [NSMutableArray array];
    // 查找 SQL 语句.
    NSString *zSql = [NSString stringWithFormat:@"SELECT * FROM dls160101 WHERE gender = '%@'", gender];
    // 创建一个 准备好的语句对象.
    sqlite3_stmt *stmt = nil;
    
    // 为准备好的语句对象赋值.(SQL 语句内容).
    int result = sqlite3_prepare_v2(db, zSql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"开始查询");
        int i = 0;
        // 当有下一行数据时, 继续执行检索.
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            i++;
            // 查询条件匹配, 使用函数簇 将需要的列值取出来.
            const unsigned char * name = sqlite3_column_text(stmt, 1);
            const unsigned char * gender = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            
            // 创建 model 赋值
            ModelOfStudent *stu = [[ModelOfStudent alloc]init];
            stu.name = [NSString stringWithUTF8String:(const char *)name];
            stu.gender = [NSString stringWithUTF8String:(const char *)gender];
            stu.age = age;
            // 添加到数组中.
            [array addObject:stu];
        }
        NSLog(@"查询结束, 有%d条记录匹配", i);
        
    }
    
    else {
        NSLog(@"未能启动查询, 错误码:% d", result);
    }
 
    // 销毁 stmt 对象.(内存管理)
    sqlite3_finalize(stmt);
    
    return array;
    


}

/** 删除表格  API: sqlite3_exec */
- (void)dropTable {
    // 删除表格 SQL语句.
    NSString *sql = @"DROP TABLE dls160101";
    
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除表格成功");
    }
    else {
        NSLog(@"删除表格失败, 错误码:%d", result);
    }
    

}

/** 关闭数据库 API: sqlite3_close */
- (void)closeDB {
    
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
        
        // sqlite 对象指针指向空.
        db = nil;
    }
    else {
        NSLog(@"关闭失败, 错误码:%d", result);
    }



}

@end
